package edu.uc.persistence;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import edu.uc.dto.Review;

/**
 * Creates the general user functions.
 * 
 * >> save (stores gather variables as a review object in local database table REVIEWS)
 * >> fetchReview (searches database table REVIEWS for review object with matching ID)
 * >> getReviews (pulls all reviews from database into a hashmap)
 * >> getUserReviews (pulls all user-specific reviews from database into hashmap)
 * >> logon (checks User object against database USERS and sets SessionUser global values)
 * 
 * @author Robert
 *
 */
public class ReviewDAO extends SQLiteOpenHelper implements ReviewDAOInterface {

	// Define String Variables
	private static final String REVIEWS = "reviews";
	private static final String SQL_ID = "_id";
	private static final String USER_ID = "userId";
	private static final String PICTURE_ID = "pictureId";
	private static final String RESTAURANT_NAME = "restaurantName";
	private static final String RESTAURANT_REVIEW = "restaurantReview";
	private static final String RESTAURANT_RATING = "restaurantRating";

	// Define DB Variables
	private static final int DATABASE_VERSION = 2;
	private static final String DATABASE_NAME = "foodview";
	private final String CLASS_NAME = getClass().getName();
	
	// Declare new object
	ReviewDAO testNew;

	// Create Context
	public ReviewDAO(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// CREATE TABLE statement
		String createReviewDB = "CREATE TABLE "+REVIEWS+" ("+SQL_ID+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +USER_ID+ " TEXT NOT NULL, "+PICTURE_ID+" TEXT NOT NULL, "+RESTAURANT_NAME+" TEXT NOT NULL, "+RESTAURANT_REVIEW+" TEXT NOT NULL, "+RESTAURANT_RATING+" TEXT NOT NULL);";
		Log.d(CLASS_NAME, createReviewDB);

		// Log creation
		Log.d(CLASS_NAME, "Creating Reviews table in DB.");
		
		// Execute statement
		db.execSQL(createReviewDB);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		String upgradeQuery = "CREATE TABLE "+REVIEWS+" ("+SQL_ID+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +USER_ID+ " TEXT NOT NULL, "+PICTURE_ID+" TEXT NOT NULL, "+RESTAURANT_NAME+" TEXT NOT NULL, "+RESTAURANT_REVIEW+" TEXT NOT NULL, "+RESTAURANT_RATING+" TEXT NOT NULL);";
		Log.d(CLASS_NAME, upgradeQuery);

		if (oldVersion == 1 && newVersion == 2) {
			db.execSQL(upgradeQuery);
		}
	}

	@Override
	public List<Review> getReviews() throws Exception {

		// Create the return hash
		List<Review> allReviews = new ArrayList<Review>();

		// SQL statement
		String selectSQL = "SELECT * FROM " + REVIEWS ;

		// Log statement
		Log.d(CLASS_NAME, selectSQL);

		// Create the result set
		Cursor cursor = getReadableDatabase().rawQuery(selectSQL, null);

		// Iterate over the results
		if (cursor.getCount() > 0) {
			Log.i(CLASS_NAME, cursor.getCount() + " results returned");

			// Move the cursor to the very first result.
			cursor.moveToFirst();

			// Iterate over our results.  Keep iterating as long as we're not after the last row.
			while(!cursor.isAfterLast()) {

				// Define user object
				Review thisReview = new Review();

				// Fill the user table with current user
				thisReview.setSqlId(cursor.getLong(0));
				thisReview.setUserId(cursor.getString(1));
				thisReview.setPictureId(cursor.getString(2));
				thisReview.setRestaurantName(cursor.getString(3));
				thisReview.setRestaurantReview(cursor.getString(4));
				thisReview.setRestaurantRating(cursor.getString(5));

				// Add the user
				allReviews.add(thisReview);

				// Log the user
				Log.d(CLASS_NAME, thisReview.toString());

				// Continue the loop
				cursor.moveToNext();

			}
			// Close the process at the end
			cursor.close();

		} else {
			Log.i(CLASS_NAME, "No results returned");			
		}

		return allReviews;
	}

	@Override
	public Review fetchReview(String SQLID) throws Exception {
		// create a return value.
		Review FetchedReview = new Review();

		// the SQL to retrieve plants from our database.
		String selectSQL = "SELECT * FROM " + REVIEWS + " WHERE " + USER_ID + "='" + SQLID + "'";

		// log the sql statement
		Log.d(CLASS_NAME, selectSQL);

		// get a cursor, which is like a ResultSet.  It allows us to iterate over results.
		Cursor cursor = getReadableDatabase().rawQuery(selectSQL, null);

		// iterate over results.
		if (cursor.getCount() > 0) {
			Log.i(CLASS_NAME, cursor.getCount() + " results returned");
			// move the cursor to the very first result.
			cursor.moveToFirst();

			// populate
			FetchedReview.setReviewId(cursor.getString(0));
			FetchedReview.setUserId(cursor.getString(1));
			FetchedReview.setPictureId(cursor.getString(2));
			FetchedReview.setRestaurantName(cursor.getString(3));
			FetchedReview.setRestaurantReview(cursor.getString(4));
			FetchedReview.setRestaurantRating(cursor.getString(5));

			// close the cursor.
			cursor.close();

		} else {
			Log.i(CLASS_NAME, "No results returned");
		}

		return FetchedReview;
	}

	public List<Review> getUserReviews(String userId) throws Exception {
		// create a return value.
		List<Review> userReviews = new ArrayList<Review>();

		// the SQL to retrieve plants from our database.
		String selectSQL = "SELECT * FROM " + REVIEWS + " WHERE " + USER_ID + "='" + userId  + "'";

		Log.d(CLASS_NAME, selectSQL);

		// get a cursor, which is like a ResultSet.  It allows us to iterate over results.
		Cursor cursor = getReadableDatabase().rawQuery(selectSQL, null);

		// iterate over results.
		if (cursor.getCount() > 0) {
			Log.i(CLASS_NAME, cursor.getCount() + " results returned");
			// move the cursor to the very first result.
			cursor.moveToFirst();

			// iterate over our results.  Keep iterating as long as we're not after the last row.
			while(!cursor.isAfterLast()) {
				// make a new plant.
				Review thisReview = new Review();

				// populate
				thisReview.setReviewId(cursor.getString(0));
				thisReview.setUserId(cursor.getString(1));
				thisReview.setPictureId(cursor.getString(2));
				thisReview.setRestaurantName(cursor.getString(3));
				thisReview.setRestaurantReview(cursor.getString(4));
				thisReview.setRestaurantRating(cursor.getString(5));

				// add to the collection.
				userReviews.add(thisReview);

				// move the cursor to the next location.
				cursor.moveToNext();

			}
			// close the cursor.
			cursor.close();

		} else {
			Log.i(CLASS_NAME, "No results returned");			
		}

		return userReviews;
	}

	@Override
	public void save(Review review) throws Exception {
		// Create a ContentValues, which is like a HashMap for data that we want to store in the database.

		try {
			
			ContentValues newRestaurant = new ContentValues();
			newRestaurant.put(USER_ID, review.getUserId());
			newRestaurant.put(PICTURE_ID, review.getPictureId());
			newRestaurant.put(RESTAURANT_NAME, review.getRestaurantName());
			newRestaurant.put(RESTAURANT_REVIEW, review.getRestaurantReview());
			newRestaurant.put(RESTAURANT_RATING, review.getRestaurantRating());

			// store it in the database
			long sqlId = getWritableDatabase().insert(REVIEWS, null, newRestaurant);

			// now, update the plant with the SQL ID.
			review.setSqlId(sqlId);

		} catch (Exception e) {
			Log.e(getClass().getName(), "Error saving new review: " + e.getMessage());
			e.printStackTrace();
		}
	}
}
